<?php
// P. Couchet 25/05/2011
class model {
	function model($cnxOpt, $user, $pass){
		try {
			$this->cnxDB = new PDO($cnxOpt, $user, $pass, array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
			$this->cnxDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$this->cnxDB->query("SET NAMES 'utf8'");
			}
		catch (PDOException $dbex) {die("Erreur de connexion : " . $dbex->getMessage() );}
	}
	// dans status.php
	function status($bdd, $data){
		try {
			// Auteurs
			$req = "SELECT COUNT(DISTINCT `lnfn`) FROM `$data`";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$authorResults = $prep->fetch();
			// Contributions
			$req = "SELECT COUNT(*) FROM `$data`";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$status = $prep->fetch();
		} catch (Exception $e) {
	    echo 'Erreur dans une des requêtes de status<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return array('authorResults'=>$authorResults[0], 'totalResults'=>$status[0]);
	}
	// appelé de aAction.php, sAction.php, lAction.php
	function selectLimitArray($selectArray, $from, $whereLikeArray, $whereLikeBool, $orderBy, $limitBegin, $limitLength){
		try {
			// Fabrication de select
			$selectValue = '';
			foreach($selectArray as $key=>$value) $selectValue .= "`$key` , ";
			$selectValue = rtrim($selectValue, " , ");
			// Fabrication de whereLikeValue (range de date possible)
			$whereLikeValue = '(';
			foreach($whereLikeArray as $key=>$value){
				$whereLikeValue .= "(`$key` LIKE '".addslashes($value)."'";
				if ($key=='yrvol' and (!empty($_GET['yrvolEnd']) and ($_GET['yrvolEnd']>=$_GET['yrvol']))){
					$i=$_GET['yrvol'];
					while($i<=$_GET['yrvolEnd']){
						$whereLikeValue .= " OR (`$key` LIKE '$i')";
						$i++;
					}
				}
				$whereLikeValue .= ") $whereLikeBool ";
			}
			$whereLikeValue = rtrim($whereLikeValue, " $whereLikeBool ");
			$whereLikeValue .= ')';
			// Auteurs
			$req = "SELECT COUNT(DISTINCT `lnfn`) FROM `$from`";
			if(!empty($whereLikeValue)) $req .= "WHERE $whereLikeValue";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$authorResults = $prep->fetch();
			// Contributions
			$req = "SELECT $selectValue FROM `$from` WHERE $whereLikeValue ";
			if(isset($orderBy))$req .= "ORDER BY $orderBy";
			$prep = $this->cnxDB->prepare($req);
			$prep->execute();
			$totalResults = $prep->rowCount();
			$entry = $prep->fetchAll();
//print_r($result);exit;
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		// Résultats
		return array('totalResults'=>$totalResults,'entry'=>$entry,'authorResults'=>$authorResults[0]);
	}
	// $select est un champ
	function selectDistinct($select, $from){
		try {
			$req = "SELECT DISTINCT $select FROM `$from` ORDER BY `$select`";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	// Statistiques sur la nationalité
	function statisticsNat($data){
		try {
			$req = "SELECT nat, count(*) as number FROM `$data` GROUP BY nat order by number desc,nat";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	// Statistiques sur le nom
	function statisticsLnFnRedacteur($data){
		try {
			$req = "SELECT lnfn, COUNT(*) AS number, yrvol FROM `$data` WHERE `job`='Redacteur' GROUP BY lnfn ORDER BY yrvol";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	function statisticsLnFnMax($data){
		try {
			$req = "SELECT lnfn, COUNT(*) AS number FROM `$data` GROUP BY lnfn ORDER BY number DESC LIMIT 0,20";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}
	function statisticsDate($data){
		try {
			$req = "SELECT yrvol, COUNT(*) AS number FROM `$data` GROUP BY yrvol ORDER BY yrvol";
			$res = $this->cnxDB->query($req);
			$result = $res->fetchAll();
		} catch (Exception $e) {
	    echo 'Erreur dans la requête : '.$req.'<br/>';
	    echo $e->getMessage().'<br/>';
	    echo $req;
		}
		return $result;
	}

}

?>